/*-----------------------------------------------------------------------------------
 *
 * 프로그램명	: HomePageDAO.java
 * Version		: 1.0
 * 작성일 		: 2006-08-16
 * 작성자		:
 * 수정일 		:
 * 수정자		:
 * 설명		 	:
 * 기능		 	:
 *
 * Copyright (c) 2000 by H&H  All Rights Reserved.
 *
-------------------------------------------------------------------------------------*/
package com.welfare.admin.service;


import java.util.*;
import java.sql.*;

import oracle.jdbc.driver.OracleResultSet;

import com.welfare.common.CommonUtil;
import com.zll.framework.db.DBManager;
import com.zll.framework.model.HashBox;
import com.zll.framework.model.HashList;
import com.zll.framework.util.StringUtil;



public class ServiceDAO
{
	public HashList selectServiceList(HashMap infos) throws Exception
	{
        return DBManager.executeQueryByPaging("/service.xml", "select_service_list", infos);
	}

	public HashList selectServiceInfo(HashMap infos) throws Exception
	{
        return DBManager.executeQuery("/service.xml", "select_service_info", infos);
	}
	
	public HashList selectServiceDocuList(HashMap infos) throws Exception
	{
        return DBManager.executeQuery("/service.xml", "select_service_docu_list", infos);
	}
	
	public HashList selectServiceCountMonthly(HashMap infos) throws Exception
	{
        return DBManager.executeQuery("/service.xml", "select_service_count_monthly", infos);
	}
	
	
	/**
	 * 서비스 저장
	 *
	 * @param	infos
	 * @return	boolean
	 */
	public void saveService(HashBox box) throws Exception
    {
		Connection conn = null;
		PreparedStatement   pstmt = null;
		ResultSet rs = null;
		
        try
		{
			//conn = DBManager.getConnectionByDriver();
        	conn = DBManager.getConnection();
            

			HashMap param = box.getParam();
			

			if ("D".equals(param.get("job_type"))) {
	       		DBManager.executeUpdate("/service.xml", "delete_service", param, conn);
	        } 
			else {
	        	
	        	param.put("srch_age", 		box.getCheckedValue("srch_age"));
				param.put("srch_target", 	box.getCheckedValue("srch_target"));
				param.put("srch_family", 	box.getCheckedValue("srch_family"));
				param.put("srch_income", 	box.getCheckedValue("srch_income"));
				param.put("srch_support", 	box.getCheckedValue("srch_support"));
				param.put("srch_needs", 	box.getCheckedValue("srch_needs"));
				
				if ("02".equals(param.get("service_gubn"))) {	//교육프로그램
					param.put("srch_prg_time", 		box.getCheckedValue("srch_prg_time"));
					param.put("srch_prg_weekday", 	box.getCheckedValue("srch_prg_weekday"));
				} else {
					param.put("prg_period",		"");
					param.put("srch_prg_time", 		"");
					param.put("srch_prg_weekday", 	"");
				}
				
				String sql = "select service_info, selection, extra_1, extra_2 from wfr_service where service_id = ?";
				
				if ("C".equals(param.get("job_type"))) {
					String serviceId = DBManager.executeQuery("/service.xml", "select_service_id", param).get("service_id", 0);
					param.put("service_id", serviceId);
		    		DBManager.executeUpdate("/service.xml", "insert_service", param, conn);

		    		// Write CLOB Data
		    		pstmt = conn.prepareStatement(sql);
		    		pstmt.setString(1, serviceId);
		    		rs = pstmt.executeQuery();
		    		
		            if (rs.next()) {
		            	CommonUtil.updateCLOB(((OracleResultSet)rs).getCLOB("service_info"), StringUtil.nvl(param.get("service_info")));
		            	CommonUtil.updateCLOB(((OracleResultSet)rs).getCLOB("selection"), StringUtil.nvl(param.get("selection")));
		            	CommonUtil.updateCLOB(((OracleResultSet)rs).getCLOB("extra_1"), StringUtil.nvl(param.get("extra_1")));
		            	CommonUtil.updateCLOB(((OracleResultSet)rs).getCLOB("extra_2"), StringUtil.nvl(param.get("extra_2")));
		            }
				}
				else if ("U".equals(param.get("job_type"))) {
					DBManager.executeUpdate("/service.xml", "update_service", param, conn);
		    		
					// Write CLOB Data
		    		pstmt = conn.prepareStatement(sql);
		    		pstmt.setString(1, (String)param.get("service_id"));
		    		rs = pstmt.executeQuery();
		    		
		            if (rs.next()) {
		            	CommonUtil.updateCLOB(((OracleResultSet)rs).getCLOB("service_info"), StringUtil.nvl(param.get("service_info")));
		            	CommonUtil.updateCLOB(((OracleResultSet)rs).getCLOB("selection"), StringUtil.nvl(param.get("selection")));
		            	CommonUtil.updateCLOB(((OracleResultSet)rs).getCLOB("extra_1"), StringUtil.nvl(param.get("extra_1")));
		            	CommonUtil.updateCLOB(((OracleResultSet)rs).getCLOB("extra_2"), StringUtil.nvl(param.get("extra_2")));
		            }
				}
				
				//구비서류
				ArrayList docuList = (ArrayList)box.getList("list");
			    for (int i = 0; i < docuList.size(); i++)
			    {
					HashMap map = (HashMap)docuList.get(i);
					if ("C".equals(map.get("job_type"))) {
						if (!"".equals(map.get("docu_name"))) {
							DBManager.executeUpdate("/service.xml", "insert_service_docu", map, conn);
						}
					} else if ("U".equals(map.get("job_type"))) {
						if (!"".equals(map.get("docu_name"))) {
							DBManager.executeUpdate("/service.xml", "update_service_docu", map, conn);
						}
					} else if ("D".equals(map.get("job_type"))) {
						DBManager.executeUpdate("/service.xml", "delete_service_docu", map, conn);
					}
				}
	        }

			conn.commit();
	    }
	    catch(Exception e)
	    {
	    	DBManager.rollback(conn);
	    	throw e;
		}
	    finally
	    {
	    	DBManager.closeConnection(rs);
	    	DBManager.closeConnection(pstmt);
	    	DBManager.closeConnection(conn);
		}
    }

	
	/**
	 * 서비스 저장
	 *
	 * @param	infos
	 * @return	boolean
	 */
	public void insertServiceCount(HashMap param) throws Exception
    {
		Connection conn = null;

        try
		{
			conn = DBManager.getConnection();

			DBManager.executeUpdate("/service.xml", "insert_service_count", param, conn);
			DBManager.executeUpdate("/service.xml", "update_service_hit_count", param, conn);

			conn.commit();
	    }
	    catch(Exception e)
	    {
	    	//접속로그 저장하다 에러가 나면 에러메세지만 남기고 계속진행..
	    	e.printStackTrace();
	    	//DBManager.rollback(conn);
	    	//throw e;
		}
	    finally
	    {
	    	DBManager.closeConnection(conn);
		}
    }


}
